﻿
'Description:计划管理业务类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yangsj
'create Date:2017-3-24
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports External.APICalling
Imports System.Text

Public Class uLMSMfgOrderBusiness
    Implements uLMSMfgOrderIBusiness

    ''' <summary>
    ''' 获取生产订单数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMfgOrderData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSMfgOrderIBusiness.GetMfgOrderData
        GetMfgOrderData = New uMESProcessResult
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        'Add by Yuxj20180522 区分装试和其他分厂
        If para.ContainsKey("ZS") Then
            paraPage.strSQL = MfgOrderSearchSQL_ZS(para)
        Else
            paraPage.strSQL = MfgOrderSearchSQL(para)
        End If

        GetMfgOrderData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 生成生产订单查询SQL
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function MfgOrderSearchSQL(ByVal para As Dictionary(Of String, String)) As String
        Dim sb As New StringBuilder
        '查询生产订单，包括订单名称，ID，数量，产品名称 
        '添加T.FACTORYMONTH,T.FACTORYSTARDATE,T.FACTORYCOMPLETIONDATE查询 tianfw 2017年11月21日 P.PRODUCTID,W.WORKFLOWID
        sb.AppendLine("SELECT Distinct T.MFGORDERNAME,T.MFGORDERID,P.PRODUCTID,W.WORKFLOWID,T.QTY,PB.PRODUCTNAME,P.PRODUCTREVISION,T.FACTORYMONTH,T.FACTORYSTARDATE,pf.productfamilyname,")
        sb.AppendLine("pf.productfamilyid,T.FACTORYCOMPLETIONDATE,P.DESCRIPTION PRODUCTDESCRIPTION,WB.WORKFLOWNAME,W.WORKFLOWREVISION,UA2.ATTRIBUTEVALUE ZBJH,p.bomid,") 'UA2.ATTRIBUTEVALUE ZBJH add tianFW 2018年3月28日
        sb.AppendLine("T.ORDERTYPEID,NVL(C.QTY, 0) CONNUMBER,T.PLANNEDSTARTDATE,T.PLANNEDCOMPLETIONDATE,UA.ATTRIBUTEVALUE MRPCONTROLER,UA1.ATTRIBUTEVALUE ENGINENO ") 'modify tianFW add UA.ATTRIBUTEVALUE MRPControler  ENGINENAME--发动机号
        sb.AppendLine(" FROM MFGORDER T")
        sb.AppendLine(" LEFT JOIN Orderstatus ost ON ost.orderstatusid = t.orderstatusid") 'add zhunan 2022.6.21
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB1  ON PB1.PRODUCTBASEID = T.PRODUCTBASEID")
        'modify by tianFW 将查订单件号整合 2018年9月26日
        'sb.AppendLine("LEFT JOIN PRODUCT P1  ON P1.PRODUCTID = T.PRODUCTID")
        sb.AppendLine("LEFT JOIN PRODUCT P  ON P.PRODUCTID = NVL(PB1.REVOFRCDID, T.PRODUCTID) ")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID")

        'add by Yuxj20180416
        sb.AppendLine(" LEFT JOIN productfamily pf ON p.productfamilyid=pf.productfamilyid ")

        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB1  ON WB1.WORKFLOWBASEID = P.WORKFLOWBASEID ")
        sb.AppendLine("LEFT JOIN WORKFLOW W  ON W.WORKFLOWID =NVL(WB1.REVOFRCDID,  P.WORKFLOWID) ")
        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB  ON WB.WORKFLOWBASEID = W.WORKFLOWBASEID ")
        'If para.Keys.Contains("MRPControler") Then 'add tianFW 2017/10/19
        sb.AppendLine("LEFT JOIN USERATTRIBUTE UA ON UA.PARENTID = T.MFGORDERID AND UA.USERATTRIBUTENAME = 'MRP控制者'")
        sb.AppendLine("LEFT JOIN USERATTRIBUTE UA1 ON UA1.PARENTID = T.MFGORDERID AND UA1.USERATTRIBUTENAME = '发动机'")
        sb.AppendLine("LEFT JOIN USERATTRIBUTE UA2 ON UA2.PARENTID = T.MFGORDERID AND UA2.USERATTRIBUTENAME = '组别件号'") '组别件查询 add tianFW 2018年3月28日
        'End If
        '查询订单所属批次的总数
        'modify by tianFW 订单开卡数只根据订单件号的批次数统计 2018年9月26日 修改为订单件号名称统计
        sb.AppendLine("LEFT JOIN (SELECT C.MFGORDERID,SUM(C.QTY) QTY,pb.PRODUCTNAME")
        sb.AppendLine("FROM CONTAINER C  ")
        sb.AppendLine("LEFT JOIN PRODUCT P   ON P.PRODUCTID = C.PRODUCTID")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID ")
        sb.AppendLine("WHERE C.MFGORDERID IS NOT NULL AND C.STATUS<>0 and c.qualificationqty=1 AND C.PARENTCONTAINERID IS NULL ")
        sb.AppendLine("AND PB.PRODUCTNAME NOT LIKE '%(H)' ")
        sb.AppendLine("GROUP BY C.MFGORDERID,pb.PRODUCTNAME) C  ON C.MFGORDERID = T.MFGORDERID and c.PRODUCTNAME = pb.PRODUCTNAME")
        sb.AppendLine("LEFT JOIN (SELECT DISTINCT FACTORYID,MFGORDERID FROM  CONTAINERCURRENTINFO) cci ON cci.MFGORDERID =T.MFGORDERID ") 'add by longb 2023-08-11
        sb.AppendLine("WHERE T.QTY>0  AND NVL(ost.orderstatusname,'打开') = '打开'") 'add zhunan 2022.6.21
        sb.AppendLine(" AND NVL(t.sendstart,'0') = 1") '过滤掉未下发的订单 add zhunan 2024.11.21

        If para.Keys.Contains("OpenNocheck") Then 'modify by longb 2023-08-11 开卡未检验理化申请使用
            sb.AppendFormat(" AND (cci.FACTORYID= '{0}' OR t.FACTORYID='{0}') ", para("FactoryID"))
            sb.AppendFormat(" AND EXISTS (SELECT 1 FROM containercurrentinfo ccci WHERE ccci.mfgorderid=T.mfgorderid AND ccci.isinstore<>1) AND NVL(C.QTY, 0)>0") 'add by longb 2023-10-09
        Else
            sb.AppendFormat(" AND T.FACTORYID= '{0}' ", para("FactoryID"))
        End If
        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND PB.PRODUCTNAME LIKE '%{0}%' ", para("ProductName"))
        End If

        'Modify by Yuxj20180322
        If para.Keys.Contains("mfgOrderName") Then
            If para.ContainsKey("mfgOrderNameLast") Then
                sb.AppendFormat(" and T.MFGORDERNAME  between '{0}' and '{1}'", para("mfgOrderName"), para("mfgOrderNameLast"))
            Else
                sb.AppendFormat(" AND T.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
            End If

        End If

        '增加日期筛选 add by zhangrj 2022-09-14
        If para.Keys.Contains("StartDate") Then
            sb.AppendFormat(" AND T.plannedstartdate >= to_date('{0}','yyyy-MM-dd') ", para("StartDate"))
        End If
        If para.Keys.Contains("EndDate") Then
            sb.AppendFormat(" AND T.plannedcompletiondate <= to_date('{0}','yyyy-MM-dd') ", para("EndDate"))
        End If

        If para.Keys.Contains("MRPControler") Then 'add tianFW 2017/10/19
            sb.AppendFormat("  AND UA.ATTRIBUTEVALUE LIKE '%{0}%'", para("MRPControler"))
        End If
        If para.Keys.Contains("OrderStatus") Then
            If para("OrderStatus").ToString = "1" Then
                sb.AppendLine(" AND NVL(C.QTY,0)<T.QTY")
            ElseIf para("OrderStatus").ToString = "2" Then
                sb.AppendLine(" AND nvl(C.QTY,0)>=T.QTY")
            End If
        End If
        If para.ContainsKey("LHContainerName") Then
            sb.AppendFormat("  AND EXISTS (SELECT 1 FROM CONTAINER con WHERE Con.PARENTCONTAINERID IS NULL AND Con.STATUS =1 AND con.MFGORDERID=t.MFGORDERID AND con.CONTAINERNAME LIKE '%{0}%')", para("LHContainerName"))
        End If
        sb.AppendLine(" order by  T.MFGORDERNAME,PB.PRODUCTNAME asc")
        MfgOrderSearchSQL = sb.ToString
    End Function

    ''' <summary>
    ''' Add by Yuxj20180520 装试查询Sql独立
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function MfgOrderSearchSQL_ZS(ByVal para As Dictionary(Of String, String)) As String
        Dim sb As New StringBuilder
        '查询生产订单，包括订单名称，ID，数量，产品名称 
        sb.AppendLine("SELECT T.MFGORDERNAME,ot.ordertypename,T.MFGORDERID,nvl(P.PRODUCTID,P1.PRODUCTID) PRODUCTID,'' WORKFLOWID,T.QTY,nvl(PB.PRODUCTNAME,PB1.PRODUCTNAME) PRODUCTNAME,nvl(P.PRODUCTREVISION,p1.PRODUCTREVISION) PRODUCTREVISION,T.FACTORYMONTH,T.FACTORYSTARDATE,'' productfamilyname,")
        sb.AppendLine("'' productfamilyid,T.FACTORYCOMPLETIONDATE,nvl(P.DESCRIPTION,P1.DESCRIPTION) PRODUCTDESCRIPTION,'' WORKFLOWNAME,'' WORKFLOWREVISION,")
        sb.AppendLine("p.bomid,ot.Notes,T.Notes MNotes,UA2.ATTRIBUTEVALUE mfgType,")
        sb.AppendLine("T.ORDERTYPEID,T.PLANNEDSTARTDATE,T.PLANNEDCOMPLETIONDATE,'' MRPCONTROLER,UA1.ATTRIBUTEVALUE ENGINENO ")
        sb.AppendLine(" FROM MFGORDER T")
        sb.AppendLine(" LEFT JOIN Orderstatus ost ON ost.orderstatusid = t.orderstatusid") 'add zhunan 2022.6.21
        sb.AppendLine(" LEFT JOIN Orderstatus os ON os.orderstatusid=t.orderstatusid ")
        sb.AppendLine(" LEFT JOIN Ordertype ot on ot.ordertypeid=t.ordertypeid")
        sb.AppendLine(" LEFT JOIN CONTAINERINSTORE ct ON ct.aufnr=t.mfgordername ") 'Modify by Yuxj20190510 入库后不显示        

        'Modify by Yuxj2019032701 增加了函数GET_SPLIT_MFGORDER_PRODUCTID 减少查询
        sb.AppendLine("LEFT JOIN PRODUCT P  ON P.PRODUCTID = GET_SPLIT_MFGORDER_PRODUCTID(t.mfgorderid) ")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID ")
        sb.AppendLine(<string>left join PRODUCTBASE PB1 on PB1.Productbaseid=t.productbaseid 
        left join Product p1 on p1.Productbaseid=PB1.Productbaseid and p1.productid=pb1.revofrcdid</string>) 'add by longb 2023-10-10
        ''add by Yuxj20180514 为装试项目修改为通过件号，关联工艺，关联机型的查询方法
        ''Modify by Yuxj20200609 发动机整机工艺取值，productworkflowmapping存在且w.phasenode IS NULL
        'sb.AppendLine("LEFT JOIN productworkflowmapping pw ON pw.productid=p.productid ")
        'sb.AppendLine("LEFT JOIN WORKFLOW W  ON W.WORKFLOWID =Pw.WORKFLOWID  ")
        'sb.AppendLine("LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid=w.workflowid ")
        'sb.AppendLine(" LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid ")
        'sb.AppendLine("LEFT JOIN WORKFLOWBASE WB1  ON WB1.WORKFLOWBASEID = P.WORKFLOWBASEID ")
        'sb.AppendLine("LEFT JOIN WORKFLOWBASE WB  ON WB.WORKFLOWBASEID = W.WORKFLOWBASEID ")
        'sb.AppendLine("LEFT JOIN USERATTRIBUTE UA ON UA.PARENTID = T.MFGORDERID AND UA.USERATTRIBUTENAME = 'MRP控制者'")
        sb.AppendLine("LEFT JOIN USERATTRIBUTE UA1 ON UA1.PARENTID = T.MFGORDERID AND UA1.USERATTRIBUTENAME = '发动机'")
        'Modify by Yuxj200190124
        sb.AppendLine("LEFT JOIN USERATTRIBUTE UA2 ON UA2.PARENTID = T.MFGORDERID AND UA2.USERATTRIBUTENAME = '订单类别'")

        'Modify by Yuxj20190422
        If para.ContainsKey("SGD") Then
            sb.AppendLine(" LEFT JOIN workflow wfff ON ua1.attributevalue=wfff.ly2modle_node ")
            sb.AppendLine(" LEFT JOIN workflowbase wfbb ON wfff.workflowbaseid=wfbb.workflowbaseid ")
        End If

        sb.AppendLine("WHERE T.QTY>0 and ct.aufnr IS  NULL  AND NVL(ost.orderstatusname,'打开') = '打开' ") 'add zhunan 2022.6.21
        'sb.AppendLine(" AND w.phasenode IS NULL ") 'Modify by yuxj20200609
        If para.ContainsKey("FactoryID") Then
            sb.AppendFormat(" AND T.FACTORYID= '{0}' ", para("FactoryID"))
        End If

        'Modify by Yuxj20200528 增加关闭打开选项
        If para.ContainsKey("OrderCloseStatus") Then
            If para("OrderCloseStatus") = "1" Then
                sb.AppendLine(" AND os.orderstatusname='关闭'")
            End If
            If para("OrderCloseStatus") = "0" Then
                sb.AppendLine(" AND (os.orderstatusid is null or os.orderstatusname='打开') ")
            End If
        End If

        'Modify by Yuxj20190422
        If para.ContainsKey("SGD") Then
            sb.AppendFormat(" and wfbb.workflowname like '%{0}%' ", para("SGD"))
            sb.AppendLine(" order by  T.MFGORDERNAME,PB.PRODUCTNAME asc")
            MfgOrderSearchSQL_ZS = sb.ToString
            Exit Function
        End If

        If para.Keys.Contains("OrderStatus") Then
            If para("OrderStatus").ToString = "1" Then
                sb.AppendLine(" AND T.MFGORDERID  not IN (select distinct mfgorderid from enginepartplan) ")
            ElseIf para("OrderStatus").ToString = "2" Then
                sb.AppendLine(" AND T.MFGORDERID IN (select distinct mfgorderid from enginepartplan) ")
            End If
        End If

        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND nvl(PB.PRODUCTNAME,PB1.PRODUCTNAME) LIKE '%{0}%' ", para("ProductName"))
        End If

        'Modify by Yuxj20180322
        If para.Keys.Contains("mfgOrderName") Then
            If para.ContainsKey("mfgOrderNameLast") Then
                sb.AppendFormat(" and T.MFGORDERNAME  between '{0}' and '{1}'", para("mfgOrderName"), para("mfgOrderNameLast"))
            Else
                sb.AppendFormat(" AND T.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
            End If

        End If

        If para.Keys.Contains("MonthPlan") Then
            sb.AppendFormat(" AND t.factorymonth = '{0}' ", para("MonthPlan"))
        End If

        '临时订单由PD14增加为'PD05','PD06','PD11','PD13','PD14'类型
        If para.ContainsKey("OrderTypeName") Then
            If para("OrderTypeIndex") = "1" Then
                sb.AppendFormat(" AND ot.OrderTypeName in ('{0}') ", para("OrderTypeName").Replace(",", "','"))
            Else
                'Modify by Yuxj20180731 正常订单时不显示临时订单
                sb.AppendFormat(" AND ot.OrderTypeName not in ('{0}') ", para("OrderTypeName").Replace(",", "','"))
            End If
        End If


        If para.Keys.Contains("ProductFamilyId") Then
            sb.AppendFormat(" AND wff.ProductFamilyListId = '{0}' ", para("ProductFamilyId"))
        End If

        'Modify by Yuxj2018年5月30日 增加发动机号查询
        If para.ContainsKey("Machine") Then
            sb.AppendFormat("  And UA1.attributevalue LIKE '%{0}%'", para("Machine"))
        End If

        'If para.Keys.Contains("MRPControler") Then 'add tianFW 2017/10/19
        '    sb.AppendFormat("  AND UA.ATTRIBUTEVALUE LIKE '%{0}%'", para("MRPControler"))
        'End If

        sb.AppendLine(" order by  T.MFGORDERNAME,PB.PRODUCTNAME asc")
        MfgOrderSearchSQL_ZS = sb.ToString
    End Function

    ''' <summary>
    ''' 根据开始时间和零件图号查询相关信息
    ''' create Wangjh
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetMonthPlanData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSMfgOrderIBusiness.GetMonthPlanData
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT pb.productname,m.qty,m.plannedstartdate,m.plannedcompletiondate,m.plannedstartdate,M.Mfgorderid," &
                          "m.mfgordername,(SELECT COUNT(containerid) FROM container c WHERE c.MFGORDERID=m.mfgorderid) UsedNum FROM mfgorder m")
        strsql.AppendLine(" LEFT JOIN CDODEFINITION CDO ON CDO.CDODEFID = M.CDOTYPEID")
        strsql.AppendLine("LEFT JOIN product p ON m.productid=p.productid")
        strsql.AppendLine("LEFT JOIN productbase pb ON p.productbaseid =pb.productbaseid")
        strsql.AppendFormat("WHERE CDO.CDONAME='MonthPlan' AND m.plannedstartdate BETWEEN to_date('{0}','yyyy-mm-dd hh24:mi:ss') AND to_date('{1}','yyyy-mm-dd hh24:mi:ss')", para("startDateData"), para("completeDateData"))
        strsql.AppendFormat("and pb.productname in '{0}'", para("productName"))
        Dim result2 As DataTable
        result2 = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        Return result2
    End Function

    ''' <summary>
    ''' 增加月计划订单
    ''' create Wangjh
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function SaveMonthPlan(ByVal paraDic As Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements uLMSMfgOrderIBusiness.SaveMonthPlanData
        Dim txnmaint = "MonthPlanMaint"
        Dim clientData As New CamstarClientAPI(paraDic("UserName"), paraDic("Password"))
        Dim dataEntityList As New List(Of ClientAPIEntity)


        Dim dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = paraDic("ProductName")
        dataEntity.ClientDataVersion = paraDic("ProductRevision")
        dataEntity.ClientDataIsROR = False
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = paraDic("Name")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = paraDic("Qty")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        ' dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = paraDic("PlannedStartDate")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        ' dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = paraDic("PlannedCompletionDate")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Factory"
        dataEntity.ClientDataValue = paraDic("FactoryName")
        dataEntityList.Add(dataEntity)

        If paraDic.ContainsKey("OrderStatus") Then
            If Not String.IsNullOrWhiteSpace(paraDic("OrderStatus")) Then
                dataEntity = New ClientAPIEntity()
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                dataEntity.ClientDataName = "OrderStatus"
                dataEntity.ClientDataValue = paraDic("OrderStatus")
                dataEntityList.Add(dataEntity)
            End If
        End If
        SaveMonthPlan = clientData.CreateModelByStandard(txnmaint + "Doc", txnmaint, "New", dataEntityList, resultInfo)
    End Function
    ''' <summary>
    ''' 修改月计划订单
    ''' create Wangjh
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateMonthPlanData(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSMfgOrderIBusiness.UpdateMonthPlanData
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE mfgorder m SET m.qty='{0}' , m.plannedcompletiondate=to_date('{1}','yyyy/mm/dd'),m.plannedstartdate=to_date('{2}','yyyy/mm/dd') WHERE m.mfgorderid='{3}' ",
                            para("newQty"), para("newEndDate"), para("startDate"), para("MfgorderID"))
        Try

            DatabaseHelper.ExecuteSql(strsql.ToString)
            UpdateMonthPlanData = True
        Catch ex As Exception
            UpdateMonthPlanData = False
        End Try


    End Function
    ''' <summary>
    ''' 删除月计划订单
    ''' create Wangjh
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function DelMonthPlanData(ByVal para As Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements uLMSMfgOrderIBusiness.DelMonthPlanData
        Dim txnmaint = "MonthPlanMaint"
        Dim clientData As New CamstarClientAPI(para("UserName"), para("Password"))
        DelMonthPlanData = clientData.DeleteModel(txnmaint & "Doc", txnmaint, para("StrName"), "", "NDO", resultInfo)
    End Function

    ''' <summary>
    ''' 获取订单的简要信息 create tianFW 2017/10/24
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMfgorderInfo(ByRef para As Dictionary(Of String, String)) As DataTable Implements uLMSMfgOrderIBusiness.GetMfgorderInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT M.MFGORDERNAME, M.MFGORDERID,P.PRODUCTID")
        strsql.AppendLine("FROM MFGORDER M")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = M.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN PRODUCT P ON P.PRODUCTID=NVL(PB.REVOFRCDID,M.PRODUCTID)")
        strsql.AppendLine("LEFT JOIN (SELECT C.MFGORDERID, SUM(C.QTY) QTY")
        strsql.AppendLine("FROM CONTAINER C")
        strsql.AppendLine("WHERE C.MFGORDERID IS NOT NULL AND C.STATUS <> 0")
        strsql.AppendLine("AND C.PARENTCONTAINERID IS NULL GROUP BY C.MFGORDERID) C ON C.MFGORDERID = M.MFGORDERID")
        strsql.AppendFormat("WHERE M.QTY > 0 AND M.QTY > NVL(C.QTY, 0)AND M.FACTORYID = '{0}'", para("FactoryID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 批量导入订单时创建订单 create tianFW2017/11/01
    ''' </summary>
    ''' <remarks></remarks>
    Public Function CreateNewMfgOrder(ByVal para As Dictionary(Of String, String), ByRef message As String) As Boolean Implements uLMSMfgOrderIBusiness.CreateNewMfgOrder
        Dim api As New CamstarClientAPI(para("UserName"), para("Password"))
        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = para("ProductName")
        dataEntity.ClientDataVersion = para("ProductRev")
        dataEntity.ClientDataIsROR = False
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("MfgOrderName")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para("MfgOrderQty")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para("PlannedStratDate")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para("PlannedEndDate")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Factory"
        dataEntity.ClientDataValue = para("FactoryName")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "OrderType"
        dataEntity.ClientDataValue = para("MfgOrderType")
        dataEntityList.Add(dataEntity)

        Dim ChildEntityList As New Dictionary(Of String, List(Of ClientAPIEntity))
        Dim m_Child As New List(Of ClientAPIEntity)

        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = "MRP控制者"
        m_Child.Add(dataEntity)
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "AttributeValue"
        dataEntity.ClientDataValue = para("MRP")
        m_Child.Add(dataEntity)
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "IsExpression"
        dataEntity.ClientDataValue = "false"
        m_Child.Add(dataEntity)
        dataEntity = New ClientAPIEntity()
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "DataType"
        dataEntity.ClientDataValue = "4"
        m_Child.Add(dataEntity)
        ChildEntityList.Add(0, m_Child)
        CreateNewMfgOrder = api.CreateModelHasSubentityList("MfgOrderMaintDoc", "MfgOrderMaint", "new", dataEntityList, "Attributes", ChildEntityList, message)
    End Function

    ''' <summary>
    ''' 更新订单中的装饰信息 create tianFW 2017年11月21日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateMfgOrderInfo(ByVal para As Dictionary(Of String, String)) Implements uLMSMfgOrderIBusiness.UpdateMfgOrderInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE MFGORDER M SET")
        If Not String.IsNullOrWhiteSpace(para("FactoryMonth")) Then
            strsql.AppendFormat(" M.FACTORYMONTH='{0}',", para("FactoryMonth"))
        End If

        'Modify by Yuxj20190116增加备注
        If para.ContainsKey("Notes") Then
            strsql.AppendFormat(" M.Notes='{0}',", para("Notes"))
        End If

        If Not String.IsNullOrWhiteSpace(para("FactoryStratDate")) Then
            strsql.AppendFormat(" M.FACTORYSTARDATE=TO_DATE('{0}','yyyy-MM-dd'),", para("FactoryStratDate"))
        End If
        If Not String.IsNullOrWhiteSpace(para("FactoryEndDate")) Then
            strsql.AppendFormat(" M.FACTORYCOMPLETIONDATE=TO_DATE('{0}','yyyy-MM-dd')", para("FactoryEndDate"))
        End If
        If strsql.ToString.Trim().Last() = "," Then
            Dim value = strsql.ToString.Trim().Remove(strsql.Length - 1, 1) '删除‘,’
            strsql.Clear()
            strsql.AppendLine(value)
        End If
        strsql.AppendFormat(" WHERE M.MFGORDERID='{0}'", para("MfgOrderID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取父子订单最上层父订单 create tianFW 2018年1月15日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetFatherMfgOrderInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSMfgOrderIBusiness.GetFatherMfgOrderInfo
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT T.MFGORDERID,T.MFGORDERNAME FROM MFGORDER T")
        Dim sb_ As New StringBuilder
        sb_.AppendLine("LEFT JOIN PRODUCTBASE PB1  ON PB1.PRODUCTBASEID = T.PRODUCTBASEID")
        sb_.AppendLine("LEFT JOIN PRODUCT P  ON P.PRODUCTID = NVL(PB1.REVOFRCDID, T.PRODUCTID) ")
        sb_.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        sb_.AppendLine("LEFT JOIN (SELECT C.MFGORDERID, SUM(C.QTY) QTY ")
        sb_.AppendLine("FROM CONTAINER C  ")
        sb_.AppendLine("LEFT JOIN PRODUCT P   ON P.PRODUCTID = C.PRODUCTID")
        sb_.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID ")
        sb_.AppendLine("WHERE C.MFGORDERID IS NOT NULL AND C.STATUS<>0 AND C.PARENTCONTAINERID IS NULL ")
        sb_.AppendLine("AND PB.PRODUCTNAME NOT LIKE '%(H)' ")
        sb_.AppendLine("GROUP BY C.MFGORDERID) C  ON C.MFGORDERID = T.MFGORDERID")
        sb.AppendLine(sb_.ToString())
        sb.AppendLine("WHERE T.QTY>0 ")
        sb.AppendFormat(" AND T.FACTORYID= '{0}' ", para("FactoryID"))
        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND PB.PRODUCTNAME LIKE '%{0}%' ", para("ProductName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            sb.AppendFormat(" AND T.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("OrderStatus") Then
            If para("OrderStatus").ToString = "1" Then
                sb.AppendLine(" AND NVL(C.QTY,0)<T.QTY")
            ElseIf para("OrderStatus").ToString = "2" Then
                sb.AppendLine(" AND NVL(C.QTY,0)>=T.QTY")
            End If
        End If
        Dim dt_vaule As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString()) '获取按条件查询得到的订单信息
        If dt_vaule.Rows.Count = 0 Then
            Return New uMESProcessResult
        End If
        Dim ChildOrderIDs As New StringBuilder '作为子订单ID
        Dim FatherOrderIDs As New StringBuilder
        For Each row As DataRow In dt_vaule.Rows '作为父订单ID
            If ChildOrderIDs.Length = 0 Then
                ChildOrderIDs.AppendFormat(" (UA.PARENTID='{0}'", row("MFGORDERID"))
            Else
                ChildOrderIDs.AppendFormat(" OR UA.PARENTID='{0}'", row("MFGORDERID"))
            End If
            If FatherOrderIDs.Length = 0 Then
                FatherOrderIDs.AppendFormat(" (UA.ATTRIBUTEVALUE='{0}'", row("MFGORDERNAME"))
            Else
                FatherOrderIDs.AppendFormat(" OR UA.ATTRIBUTEVALUE='{0}'", row("MFGORDERNAME"))
            End If
        Next
        ChildOrderIDs.AppendLine(")")
        FatherOrderIDs.AppendLine(")")
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT M.MFGORDERID,UA.PARENTID FROM USERATTRIBUTE UA")
        strsql.AppendLine("LEFT JOIN MFGORDER M ON M.MFGORDERNAME = UA.ATTRIBUTEVALUE")
        strsql.AppendFormat(" WHERE UA.USERATTRIBUTENAME='父订单号' AND {0} AND M.MFGORDERID IS NOT NULL", ChildOrderIDs)
        Dim dt_FatcherOrders As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString()) '获取有父订单的订单IDs
        strsql.Clear()
        strsql.AppendLine("SELECT M.MFGORDERID FROM USERATTRIBUTE UA ")
        strsql.AppendLine("LEFT JOIN MFGORDER M ON M.MFGORDERNAME = UA.ATTRIBUTEVALUE")
        strsql.AppendFormat("WHERE UA.USERATTRIBUTENAME='父订单号' AND {0} AND M.MFGORDERID IS NOT NULL GROUP BY UA.ATTRIBUTEVALUE,M.MFGORDERID", FatherOrderIDs)
        Dim dt_ChildOrders As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString()) '获取有子订单的订单
        Dim father_IDs As String = "" '有父订单id的字符串集合
        Dim son_IDs As String = "" '有子订单id的字符串集合
        GetTotalValueToString(father_IDs, dt_FatcherOrders, "PARENTID") '获取有父订单的父订单ID
        GetTotalValueToString(son_IDs, dt_ChildOrders, "MFGORDERID") '获取有子订单的订单信息
        If father_IDs = "" And son_IDs = "" Then
            Return New uMESProcessResult
            Exit Function
        End If
        Dim fatherID_list As New List(Of String) '是最高级父订单的订单ID列表
        Dim hasfatherID_list As New List(Of String) '有父IDs的订单ID表
        For Each row As DataRow In dt_vaule.Rows
            If father_IDs <> "" And father_IDs.Contains(row("MFGORDERID").ToString()) Then '存在父订单
                Dim existYse = False
                For Each dr As DataRow In dt_FatcherOrders.Rows
                    If dr("PARENTID").ToString() = row("MFGORDERID") Then '如果存在父订单则将父订单添加到hasfatherID_list
                        For i As Integer = 0 To hasfatherID_list.Count - 1
                            If hasfatherID_list(i) = dr("MFGORDERID").ToString() Then '存在的父订单就不再添加
                                existYse = True
                                Exit For
                            End If
                        Next
                        If existYse Then
                            Exit For
                        End If
                        hasfatherID_list.Add(dr("MFGORDERID").ToString())
                        Exit For
                    End If
                Next
                Continue For
            ElseIf son_IDs <> "" And son_IDs.Contains(row("MFGORDERID").ToString()) Then '如果没有父订单有子订单添加到fatherID_list
                fatherID_list.Add(row("MFGORDERID").ToString())
            End If
        Next
        If hasfatherID_list.Count > 0 Then
            GetFatherDetail(fatherID_list, hasfatherID_list)
        End If
        Dim mfgOrderIDs As New StringBuilder
        If fatherID_list.Count > 0 Then
            For i As Integer = 0 To fatherID_list.Count - 1
                If mfgOrderIDs.Length = 0 Then
                    mfgOrderIDs.AppendFormat(" AND (T.MFGORDERID='{0}'", fatherID_list(i))
                Else
                    mfgOrderIDs.AppendFormat(" OR T.MFGORDERID='{0}'", fatherID_list(i))
                End If
            Next
            mfgOrderIDs.AppendLine(")")
        End If
        sb.Clear()
        If mfgOrderIDs.Length = 0 Then
            Return New uMESProcessResult
            Exit Function
        End If
        GetFatherMfgOrderInfo = GetValueMfgOrdeTable(mfgOrderIDs.ToString(), para)
    End Function

    ''' <summary>
    ''' 根据订单ID获取对应的订单信息 create tianFW 2018年1月16日
    ''' </summary>
    ''' <param name="mfgOrderIDs"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetValueMfgOrdeTable(ByVal mfgOrderIDs As String, ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT T.MFGORDERNAME,T.MFGORDERID,P.PRODUCTID,W.WORKFLOWID,T.QTY,PB.PRODUCTNAME,P.PRODUCTREVISION,T.FACTORYMONTH,T.FACTORYSTARDATE,")
        sb.AppendLine("T.FACTORYCOMPLETIONDATE,P.DESCRIPTION PRODUCTDESCRIPTION,WB.WORKFLOWNAME,W.WORKFLOWREVISION,")
        sb.AppendLine("T.ORDERTYPEID,NVL(C.QTY, 0) CONNUMBER,T.PLANNEDSTARTDATE,T.PLANNEDCOMPLETIONDATE") 'modify tianFW add UA.ATTRIBUTEVALUE MRPControler  ENGINENAME--发动机号
        sb.AppendLine(" FROM MFGORDER T")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB1  ON PB1.PRODUCTBASEID = T.PRODUCTBASEID")
        sb.AppendLine("LEFT JOIN PRODUCT P  ON P.PRODUCTID = NVL(PB1.REVOFRCDID, T.PRODUCTID) ")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        sb.AppendLine("LEFT JOIN (SELECT C.MFGORDERID, SUM(C.QTY) QTY ")
        sb.AppendLine("FROM CONTAINER C  ")
        sb.AppendLine("LEFT JOIN PRODUCT P   ON P.PRODUCTID = C.PRODUCTID")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID ")
        sb.AppendLine("WHERE C.MFGORDERID IS NOT NULL AND C.STATUS<>0 AND C.PARENTCONTAINERID IS NULL ")
        sb.AppendLine("AND PB.PRODUCTNAME NOT LIKE '%(H)' ")
        sb.AppendLine("GROUP BY C.MFGORDERID) C  ON C.MFGORDERID = T.MFGORDERID")
        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB1  ON WB1.WORKFLOWBASEID = P.WORKFLOWBASEID ")
        sb.AppendLine("LEFT JOIN WORKFLOW W  ON W.WORKFLOWID =NVL(WB1.REVOFRCDID,  P.WORKFLOWID) ")
        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB  ON WB.WORKFLOWBASEID = W.WORKFLOWBASEID ")
        sb.AppendLine("WHERE T.QTY>0 ")
        sb.AppendLine(mfgOrderIDs)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 根据表将表中关键字组成字符串 create tianFW 2018年1月15日
    ''' </summary>
    ''' <param name="strvalue">字符串</param>
    ''' <param name="dt">表</param>
    ''' <param name="key">关键字</param>
    ''' <remarks></remarks>
    Sub GetTotalValueToString(ByRef strvalue As String, ByVal dt As DataTable, ByVal key As String)
        For Each row In dt.Rows
            If strvalue.Length = 0 Then
                strvalue = row(key).ToString()
            Else
                strvalue += "," & row(key).ToString()
            End If
        Next
    End Sub

    ''' <summary>
    ''' 递归获取最高级父订单订单ID列表 create tianFW 2018年1月15日
    ''' </summary>
    ''' <param name="fatherID_list">最高级父订单列表</param>
    ''' <param name="hasfatherID_list">有父订单的父订单ID列表</param>
    ''' <remarks></remarks>
    Sub GetFatherDetail(ByRef fatherID_list As List(Of String), ByVal hasfatherID_list As List(Of String))
        Dim ChildOrderIDs As New StringBuilder '作为子订单ID
        For i As Integer = 0 To hasfatherID_list.Count - 1
            If ChildOrderIDs.Length = 0 Then
                ChildOrderIDs.AppendFormat(" (UA.PARENTID='{0}'", hasfatherID_list(i))
            Else
                ChildOrderIDs.AppendFormat(" OR UA.PARENTID='{0}'", hasfatherID_list(i))
            End If
        Next
        ChildOrderIDs.AppendLine(")")
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT UA.ATTRIBUTEVALUE FATHERORDERNAME,M.MFGORDERID FATHERORDERID,UA.PARENTID MFGORDERID FROM USERATTRIBUTE UA")
        strsql.AppendLine("LEFT JOIN MFGORDER M ON M.MFGORDERNAME = UA.ATTRIBUTEVALUE")
        strsql.AppendFormat(" WHERE UA.USERATTRIBUTENAME='父订单号' AND {0} AND UA.ATTRIBUTEVALUE IS NOT NULL", ChildOrderIDs)
        Dim dt_FatcherOrders As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString()) '获取有父订单的订单IDs
        If dt_FatcherOrders.Rows.Count > 0 Then '存在父订单
            Dim hasfatherID_list_ As New List(Of String) '新的有父订单ID的订单ID列表
            Dim continue_ As Boolean = False
            For i As Integer = 0 To hasfatherID_list.Count - 1
                continue_ = False
                For Each dr As DataRow In dt_FatcherOrders.Rows
                    If dr("FATHERORDERID") = dr("MFGORDERID") Then '父订单号是自己，这样会无限递归
                        Exit Sub
                    End If
                    If hasfatherID_list(i).ToString() = dr("MFGORDERID").ToString() Then '是否存在父订单ID的订单
                        For j As Integer = 0 To hasfatherID_list_.Count - 1
                            If hasfatherID_list_(j) = dr("FATHERORDERID").ToString() Then '验证存在父订单的父订单ID是否已添加，存在则不再添加
                                continue_ = True
                                Exit For
                            End If
                        Next
                        If continue_ = True Then '已经存在父订单
                            Exit For
                        End If
                        hasfatherID_list_.Add(dr("FATHERORDERID").ToString())
                        continue_ = True
                        Exit For
                    End If
                Next
                If continue_ Then '存在父订单
                    Continue For
                Else '不存在父订单
                    For k As Integer = 0 To fatherID_list.Count - 1
                        If fatherID_list(k).ToString() = hasfatherID_list(i).ToString() Then
                            continue_ = True
                            Continue For
                        End If
                    Next
                    If continue_ Then
                        Continue For
                    End If
                    fatherID_list.Add(hasfatherID_list(i)) '没有父订单ID的添加到最高级订单ID列表中
                End If
            Next
            GetFatherDetail(fatherID_list, hasfatherID_list_)
        Else
            Dim continue_ = False
            For i As Integer = 0 To hasfatherID_list.Count - 1
                continue_ = False
                For j As Integer = 0 To fatherID_list.Count - 1
                    If hasfatherID_list(i) = fatherID_list(j) Then
                        continue_ = True
                        Exit For
                    End If
                Next
                If continue_ Then
                    Continue For
                End If
                fatherID_list.Add(hasfatherID_list(i))
            Next
        End If
    End Sub

    ''' <summary>
    ''' 获取子订单信息 byFatherID create tianFW 2018年1月16日
    ''' </summary>
    ''' <param name="fatherOrderID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSonMfgOrderInfo(ByVal fatherOrderID As String, ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSMfgOrderIBusiness.GetSonMfgOrderInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT UA.PARENTID FROM USERATTRIBUTE UA WHERE")
        strsql.AppendFormat(" UA.USERATTRIBUTENAME='父订单号' AND UA.ATTRIBUTEVALUE='{0}' AND UA.PARENTID IS NOT NULL", fatherOrderID)
        Dim son_dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        If son_dt.Rows.Count = 0 Then
            Return New DataTable
        End If
        Dim mfgOrderIDs As New StringBuilder
        For Each row As DataRow In son_dt.Rows
            If mfgOrderIDs.Length = 0 Then
                mfgOrderIDs.AppendFormat(" AND (T.MFGORDERID='{0}'", row("PARENTID").ToString())
            Else
                mfgOrderIDs.AppendFormat(" OR T.MFGORDERID='{0}'", row("PARENTID").ToString())
            End If
        Next
        mfgOrderIDs.AppendLine(")")
        Dim Valuedt = GetValueMfgOrdeTable(mfgOrderIDs.ToString(), para)
        If Valuedt.Result Then
            Return Valuedt.DBTable
        Else
            Return New DataTable
        End If
    End Function

    ''' <summary>
    ''' 根据订单ID获取批次信息
    ''' </summary>
    ''' <param name="mfgOrderID"></param>
    ''' <returns></returns>
    ''' <remarks>modify by tianFW 添加查询批次件号信息 2018年7月26日</remarks>
    Function GetContainerInfoByMfgOrderID(ByVal mfgOrderID As String) As DataTable Implements uLMSMfgOrderIBusiness.GetContainerInfoByMfgOrderID
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT C.CONTAINERNAME,C.CONTAINERTYPE,C.ISSELFMFG,C.MFGORDERID,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,WF.WORKFLOWID,WF.WORKFLOWREVISION,EM.FULLNAME,C.QTY,")
        strsql.AppendLine("C.TOGETHERFLAG,C.UOMID,C.WORKCENTERID,C.TEAMID,UA1.ATTRIBUTEVALUE CHECKTECHNIQUE,UA2.ATTRIBUTEVALUE BEGINSTEP,WB.WORKFLOWNAME,C.CONTAINERID,C.MFGBATCH,C.CONTAINERCOMMENTS")
        strsql.AppendLine(",p.productid,p.productrevision prevision,pb.productname pname,p.description pdesc")
        strsql.AppendLine("FROM MFGORDER MD ")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.MFGORDERID=MD.MFGORDERID")
        strsql.AppendLine("LEFT JOIN USERATTRIBUTE UA1 ON UA1.PARENTID = C.CONTAINERID AND UA1.USERATTRIBUTENAME='CheckTechnique'")
        strsql.AppendLine("LEFT JOIN USERATTRIBUTE UA2 ON UA2.PARENTID = C.CONTAINERID AND UA2.USERATTRIBUTENAME='BeginStep'")
        strsql.AppendLine("LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID = C.MFGWORKFLOWID")
        strsql.AppendLine("LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID = WF.WORKFLOWBASEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEENAME = UA1.ATTRIBUTEVALUE")
        strsql.AppendLine("LEFT JOIN product p ON p.productid = c.productid")
        strsql.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strsql.AppendFormat(" WHERE MD.MFGORDERID='{0}'", mfgOrderID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 根据批次ID获取子工艺工艺表信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSubWorkflowInfoByContainerID(ByVal containerID As String) As DataTable Implements uLMSMfgOrderIBusiness.GetSubWorkflowInfoByContainerID
        Dim strsql As New StringBuilder
        strsql.AppendFormat("SELECT * FROM SUBWORKFLOWCHECKINFO SCO WHERE SCO.CONTAINERID='{0}'", containerID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取机型列表Add by Yuxj20180607
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProductFamilyList() As DataTable Implements uLMSMfgOrderIBusiness.GetProductFamilyList
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT pf.productfamilyid,pf.productfamilyname FROM productfamily pf GROUP BY pf.productfamilyid,pf.productfamilyname order by productfamilyname ")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function

    ''' <summary>
    ''' 获取订单类型类别Add by Yuxj20180607
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgOrderTypeList() As DataTable Implements uLMSMfgOrderIBusiness.GetMfgOrderTypeList
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT ot.ordertypeid,ot.ordertypename FROM Ordertype ot GROUP by ot.ordertypeid,ot.ordertypename order by ordertypename asc ")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function

    ''' <summary>
    ''' 订单关闭与打开数据查询 add zhunan 2022.6.17
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMfgorderOpenOrCloseData(ByVal para As Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSMfgOrderIBusiness.GetMfgorderOpenOrCloseData
        GetMfgorderOpenOrCloseData = New uMESProcessResult
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        Dim sql As String = <string>
SELECT f.factoryname,f.factoryid,mfg.mfgorderid,mfg.mfgordername,mfg.qty,u.attributevalue EngineNo,pb.productname,p.description productdescription,p.productid,
       mfg.plannedstartdate,mfg.plannedcompletiondate,od.ordertypename,DECODE(ost.orderstatusname,'关闭','关闭','打开') status
FROM mfgorder mfg
left join productbase pb1 on pb1.productbaseid = mfg.productbaseid
left join product p on p.productid = nvl(pb1.revofrcdid, mfg.productid) 
left join productbase pb on pb.productbaseid = p.productbaseid
left join userattribute u on u.parentid = mfg.mfgorderid and u.userattributename = '发动机'
LEFT JOIN Ordertype od ON od.ordertypeid = mfg.ordertypeid
LEFT JOIN Orderstatus ost ON ost.orderstatusid = mfg.orderstatusid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
WHERE 1=1 
                          </string>.Value
        If para.Keys.Contains("factoryid") Then
            sql += String.Format(" AND mfg.factoryid = '{0}'", para("factoryid").ToString())
        End If
        If para.Keys.Contains("mfgordername") Then
            sql += String.Format(" AND mfg.mfgordername like '%{0}%'", para("mfgordername").ToString())
        End If
        If para.Keys.Contains("productname") Then
            sql += String.Format(" AND pb.productname like '%{0}%'", para("productname").ToString())
        End If
        If para.Keys.Contains("engineno") Then
            sql += String.Format(" AND u.attributevalue like '%{0}%'", para("engineno").ToString())
        End If
        If para.Keys.Contains("startdate") Then
            sql += String.Format(" AND mfg.plannedcompletiondate >= to_date('{0}','yyyy/mm/dd')", para("startdate").ToString())
        End If
        If para.Keys.Contains("enddate") Then
            sql += String.Format(" AND mfg.plannedcompletiondate <= to_date('{0}','yyyy/mm/dd')", para("enddate").ToString())
        End If
        If para.ContainsKey("status") Then
            sql += String.Format(" AND NVL(ost.orderstatusname,'打开') = '{0}'", para("status").ToString())
        End If
        paraPage.strSQL = sql
        GetMfgorderOpenOrCloseData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
End Class
